<!DOCTYPE HTML>
<!--
	Dimension by HTML5 UP
	html5up.net | @ajlkn
	Free for personal and commercial use under the CCA 3.0 license (html5up.net/license)
-->
<html>
 <head>
  <title>
   Dimension by HTML5 UP
  </title>
  <!-- <meta charset="utf-8" /> -->
  <!-- <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no" /> -->
  <meta charset="utf-8"/>
  <meta content="width=device-width,initial-scale=1.0" name="viewport"/>
  <link href="../../assets/css/article.css" rel="stylesheet"/>
  <link href="https://cdn.bootcss.com/highlight.js/9.15.8/styles/github.min.css" rel="stylesheet"/>
  <noscript>
   <link href="../../assets/css/noscript.css" rel="stylesheet"/>
  </noscript>
 </head>
 <body>
  <div id="app">
  </div>
  <!-- built files will be auto injected -->
 </body>
 <body class="is-preload">
  <!-- Wrapper -->
  <div id="wrapper">
   <!-- Main -->
   <div id="main">
    <article id="article">
     <h1 id="sharding-sphere-proxy">
      Sharding-Sphere Proxy 分库分表 简单示例
     </h1>
     <hr/>
     <h2 id="_1">
      简要说明
     </h2>
     <p>
      对一张简单的订单表数据表进行水平分库分表，拆分2个库，每个库16张表并在新结构在演示常见的增删改查操作
     </p>
     <h3 id="_2">
      环境配置
     </h3>
     <h4 id="mysql">
      设置MySQL
     </h4>
     <p>
      使用docker设置mysql
     </p>
     <p>
      ```shell script
     </p>
     <h1 id="mysql_1">
      启动两个mysql
     </h1>
     <p>
      docker run --name mysql11 -p 3311:3306 -e MYSQL_ROOT_PASSWORD=root -e MYSQL_ROOT_HOST=% -d mysql:latest
docker run --name mysql12 -p 3312:3306 -e MYSQL_ROOT_PASSWORD=root -e MYSQL_ROOT_HOST=% -d mysql:latest
     </p>
     <h1 id="11demo_ds_0">
      在11上创建数据库demo_ds_0
     </h1>
     <p>
      docker exec -ti mysql11 mysql -u root -p
create database demo_ds_0;
     </p>
     <h1 id="12demo_ds_1">
      在12上创建数据库demo_ds_1
     </h1>
     <p>
      docker exec -ti mysql11 mysql -u root -p
create database demo_ds_1;
     </p>
     <div class="codehilite">
      <pre><span></span><code><span class="o">####</span> <span class="n">ShardingSphere</span><span class="o">-</span><span class="n">Proxy</span> <span class="mi">5</span><span class="p">.</span><span class="mi">0</span><span class="p">.</span><span class="mi">0</span> <span class="n">alpha</span> <span class="err">设置</span>
<span class="o">&amp;</span><span class="n">ensp</span><span class="p">;</span><span class="o">&amp;</span><span class="n">ensp</span><span class="p">;</span><span class="o">&amp;</span><span class="n">ensp</span><span class="p">;</span><span class="o">&amp;</span><span class="n">ensp</span><span class="p">;</span><span class="n">docker一直不能设置成功</span><span class="err">，有点奇怪，这里就直接下载使用了</span>

<span class="o">-</span> <span class="mi">1</span><span class="p">.</span><span class="err">下载</span><span class="p">[</span><span class="n">ShardingSphere</span><span class="o">-</span><span class="n">Proxy</span><span class="p">](</span><span class="n">https</span><span class="p">:</span><span class="o">//</span><span class="n">www</span><span class="p">.</span><span class="n">apache</span><span class="p">.</span><span class="n">org</span><span class="o">/</span><span class="n">dyn</span><span class="o">/</span><span class="n">closer</span><span class="p">.</span><span class="n">cgi</span><span class="o">/</span><span class="n">shardingsphere</span><span class="o">/</span><span class="mi">5</span><span class="p">.</span><span class="mi">0</span><span class="p">.</span><span class="mi">0</span><span class="o">-</span><span class="n">alpha</span><span class="o">/</span><span class="n">apache</span><span class="o">-</span><span class="n">shardingsphere</span><span class="o">-</span><span class="mi">5</span><span class="p">.</span><span class="mi">0</span><span class="p">.</span><span class="mi">0</span><span class="o">-</span><span class="n">alpha</span><span class="o">-</span><span class="n">shardingsphere</span><span class="o">-</span><span class="n">proxy</span><span class="o">-</span><span class="n">bin</span><span class="p">.</span><span class="n">tar</span><span class="p">.</span><span class="n">gz</span><span class="p">)</span><span class="err">，下载完成后放到自己相应的目录下</span>
<span class="o">-</span> <span class="mi">2</span><span class="p">.</span><span class="err">下载</span><span class="p">[</span><span class="n">MySQL</span><span class="o">-</span><span class="k">connect</span><span class="p">.</span><span class="n">jar</span><span class="p">](</span><span class="n">https</span><span class="p">:</span><span class="o">//</span><span class="n">repo1</span><span class="p">.</span><span class="n">maven</span><span class="p">.</span><span class="n">org</span><span class="o">/</span><span class="n">maven2</span><span class="o">/</span><span class="n">mysql</span><span class="o">/</span><span class="n">mysql</span><span class="o">-</span><span class="n">connector</span><span class="o">-</span><span class="n">java</span><span class="o">/</span><span class="mi">5</span><span class="p">.</span><span class="mi">1</span><span class="p">.</span><span class="mi">47</span><span class="o">/</span><span class="n">mysql</span><span class="o">-</span><span class="n">connector</span><span class="o">-</span><span class="n">java</span><span class="o">-</span><span class="mi">5</span><span class="p">.</span><span class="mi">1</span><span class="p">.</span><span class="mi">47</span><span class="p">.</span><span class="n">jar</span><span class="p">),</span><span class="err">下载完成后将</span><span class="n">jar文件放到Sharding根目录的lib目录下</span>

<span class="o">&amp;</span><span class="n">ensp</span><span class="p">;</span><span class="o">&amp;</span><span class="n">ensp</span><span class="p">;</span><span class="o">&amp;</span><span class="n">ensp</span><span class="p">;</span><span class="o">&amp;</span><span class="n">ensp</span><span class="p">;</span><span class="err">下面需要配置两个文件：</span><span class="n">server</span><span class="p">.</span><span class="n">yaml</span><span class="err">、</span><span class="n">config</span><span class="o">-</span><span class="n">sharding</span><span class="p">.</span><span class="n">yaml</span><span class="p">,</span><span class="err">示例如下（配置都有默认示例说明的，相应进行修改即可）</span>

<span class="o">&amp;</span><span class="n">ensp</span><span class="p">;</span><span class="o">&amp;</span><span class="n">ensp</span><span class="p">;</span><span class="o">&amp;</span><span class="n">ensp</span><span class="p">;</span><span class="o">&amp;</span><span class="n">ensp</span><span class="p">;</span><span class="n">server</span><span class="p">.</span><span class="n">yaml</span>

<span class="o">```</span><span class="n">yaml</span>
<span class="o">#</span>
<span class="o">#</span> <span class="n">Licensed</span> <span class="k">to</span> <span class="n">the</span> <span class="n">Apache</span> <span class="n">Software</span> <span class="n">Foundation</span> <span class="p">(</span><span class="n">ASF</span><span class="p">)</span> <span class="k">under</span> <span class="n">one</span> <span class="k">or</span> <span class="k">more</span>
<span class="o">#</span> <span class="n">contributor</span> <span class="n">license</span> <span class="n">agreements</span><span class="p">.</span>  <span class="n">See</span> <span class="n">the</span> <span class="n">NOTICE</span> <span class="n">file</span> <span class="n">distributed</span> <span class="k">with</span>
<span class="o">#</span> <span class="n">this</span> <span class="k">work</span> <span class="k">for</span> <span class="n">additional</span> <span class="n">information</span> <span class="n">regarding</span> <span class="n">copyright</span> <span class="n">ownership</span><span class="p">.</span>
<span class="o">#</span> <span class="n">The</span> <span class="n">ASF</span> <span class="n">licenses</span> <span class="n">this</span> <span class="n">file</span> <span class="k">to</span> <span class="n">You</span> <span class="k">under</span> <span class="n">the</span> <span class="n">Apache</span> <span class="n">License</span><span class="p">,</span> <span class="k">Version</span> <span class="mi">2</span><span class="p">.</span><span class="mi">0</span>
<span class="o">#</span> <span class="p">(</span><span class="n">the</span> <span class="ss">"License"</span><span class="p">);</span> <span class="n">you</span> <span class="n">may</span> <span class="k">not</span> <span class="n">use</span> <span class="n">this</span> <span class="n">file</span> <span class="k">except</span> <span class="k">in</span> <span class="n">compliance</span> <span class="k">with</span>
<span class="o">#</span> <span class="n">the</span> <span class="n">License</span><span class="p">.</span>  <span class="n">You</span> <span class="n">may</span> <span class="n">obtain</span> <span class="n">a</span> <span class="k">copy</span> <span class="k">of</span> <span class="n">the</span> <span class="n">License</span> <span class="k">at</span>
<span class="o">#</span>
<span class="o">#</span>     <span class="n">http</span><span class="p">:</span><span class="o">//</span><span class="n">www</span><span class="p">.</span><span class="n">apache</span><span class="p">.</span><span class="n">org</span><span class="o">/</span><span class="n">licenses</span><span class="o">/</span><span class="n">LICENSE</span><span class="o">-</span><span class="mi">2</span><span class="p">.</span><span class="mi">0</span>
<span class="o">#</span>
<span class="o">#</span> <span class="n">Unless</span> <span class="n">required</span> <span class="k">by</span> <span class="n">applicable</span> <span class="n">law</span> <span class="k">or</span> <span class="n">agreed</span> <span class="k">to</span> <span class="k">in</span> <span class="n">writing</span><span class="p">,</span> <span class="n">software</span>
<span class="o">#</span> <span class="n">distributed</span> <span class="k">under</span> <span class="n">the</span> <span class="n">License</span> <span class="k">is</span> <span class="n">distributed</span> <span class="k">on</span> <span class="n">an</span> <span class="ss">"AS IS"</span> <span class="n">BASIS</span><span class="p">,</span>
<span class="o">#</span> <span class="k">WITHOUT</span> <span class="n">WARRANTIES</span> <span class="k">OR</span> <span class="n">CONDITIONS</span> <span class="k">OF</span> <span class="k">ANY</span> <span class="n">KIND</span><span class="p">,</span> <span class="n">either</span> <span class="n">express</span> <span class="k">or</span> <span class="n">implied</span><span class="p">.</span>
<span class="o">#</span> <span class="n">See</span> <span class="n">the</span> <span class="n">License</span> <span class="k">for</span> <span class="n">the</span> <span class="k">specific</span> <span class="k">language</span> <span class="n">governing</span> <span class="n">permissions</span> <span class="k">and</span>
<span class="o">#</span> <span class="n">limitations</span> <span class="k">under</span> <span class="n">the</span> <span class="n">License</span><span class="p">.</span>
<span class="o">#</span>

<span class="o">######################################################################################################</span>
<span class="o">#</span> 
<span class="o">#</span> <span class="k">If</span> <span class="n">you</span> <span class="n">want</span> <span class="k">to</span> <span class="n">configure</span> <span class="n">governance</span><span class="p">,</span> <span class="k">authorization</span> <span class="k">and</span> <span class="n">proxy</span> <span class="n">properties</span><span class="p">,</span> <span class="n">please</span> <span class="n">refer</span> <span class="k">to</span> <span class="n">this</span> <span class="n">file</span><span class="p">.</span>
<span class="o">#</span> 
<span class="o">######################################################################################################</span>
<span class="o">#</span>
<span class="o">#</span><span class="n">governance</span><span class="p">:</span>
<span class="o">#</span>  <span class="n">name</span><span class="p">:</span> <span class="n">governance_ds</span>
<span class="o">#</span>  <span class="n">registryCenter</span><span class="p">:</span>
<span class="o">#</span>    <span class="k">type</span><span class="p">:</span> <span class="n">ZooKeeper</span>
<span class="o">#</span>    <span class="n">serverLists</span><span class="p">:</span> <span class="n">localhost</span><span class="p">:</span><span class="mi">2181</span>
<span class="o">#</span>    <span class="n">props</span><span class="p">:</span>
<span class="o">#</span>      <span class="n">retryIntervalMilliseconds</span><span class="p">:</span> <span class="mi">500</span>
<span class="o">#</span>      <span class="n">timeToLiveSeconds</span><span class="p">:</span> <span class="mi">60</span>
<span class="o">#</span>      <span class="n">maxRetries</span><span class="p">:</span> <span class="mi">3</span>
<span class="o">#</span>      <span class="n">operationTimeoutMilliseconds</span><span class="p">:</span> <span class="mi">500</span>
<span class="o">#</span>  <span class="n">overwrite</span><span class="p">:</span> <span class="k">false</span>

<span class="n">authentication</span><span class="p">:</span>
  <span class="n">users</span><span class="p">:</span>
    <span class="n">root</span><span class="p">:</span>
      <span class="n">password</span><span class="p">:</span> <span class="n">root</span>
    <span class="n">sharding</span><span class="p">:</span>
      <span class="n">password</span><span class="p">:</span> <span class="n">sharding</span>
      <span class="n">authorizedSchemas</span><span class="p">:</span> <span class="n">sharding_db</span>

<span class="n">props</span><span class="p">:</span>
  <span class="k">max</span><span class="o">-</span><span class="n">connections</span><span class="o">-</span><span class="k">size</span><span class="o">-</span><span class="n">per</span><span class="o">-</span><span class="n">query</span><span class="p">:</span> <span class="mi">1</span>
  <span class="n">acceptor</span><span class="o">-</span><span class="k">size</span><span class="p">:</span> <span class="mi">16</span>  <span class="o">#</span> <span class="n">The</span> <span class="k">default</span> <span class="n">value</span> <span class="k">is</span> <span class="n">available</span> <span class="n">processors</span> <span class="k">count</span> <span class="o">*</span> <span class="mi">2</span><span class="p">.</span>
  <span class="n">executor</span><span class="o">-</span><span class="k">size</span><span class="p">:</span> <span class="mi">16</span>  <span class="o">#</span> <span class="n">Infinite</span> <span class="k">by</span> <span class="k">default</span><span class="p">.</span>
  <span class="n">proxy</span><span class="o">-</span><span class="n">frontend</span><span class="o">-</span><span class="n">flush</span><span class="o">-</span><span class="n">threshold</span><span class="p">:</span> <span class="mi">128</span>  <span class="o">#</span> <span class="n">The</span> <span class="k">default</span> <span class="n">value</span> <span class="k">is</span> <span class="mi">128</span><span class="p">.</span>
    <span class="o">#</span> <span class="k">LOCAL</span><span class="p">:</span> <span class="n">Proxy</span> <span class="n">will</span> <span class="n">run</span> <span class="k">with</span> <span class="k">LOCAL</span> <span class="n">transaction</span><span class="p">.</span>
    <span class="o">#</span> <span class="n">XA</span><span class="p">:</span> <span class="n">Proxy</span> <span class="n">will</span> <span class="n">run</span> <span class="k">with</span> <span class="n">XA</span> <span class="n">transaction</span><span class="p">.</span>
    <span class="o">#</span> <span class="n">BASE</span><span class="p">:</span> <span class="n">Proxy</span> <span class="n">will</span> <span class="n">run</span> <span class="k">with</span> <span class="n">B</span><span class="p">.</span><span class="n">A</span><span class="p">.</span><span class="n">S</span><span class="p">.</span><span class="n">E</span> <span class="n">transaction</span><span class="p">.</span>
  <span class="n">proxy</span><span class="o">-</span><span class="n">transaction</span><span class="o">-</span><span class="k">type</span><span class="p">:</span> <span class="k">LOCAL</span>
  <span class="n">proxy</span><span class="o">-</span><span class="n">opentracing</span><span class="o">-</span><span class="n">enabled</span><span class="p">:</span> <span class="k">false</span>
  <span class="n">proxy</span><span class="o">-</span><span class="n">hint</span><span class="o">-</span><span class="n">enabled</span><span class="p">:</span> <span class="k">false</span>
  <span class="n">query</span><span class="o">-</span><span class="k">with</span><span class="o">-</span><span class="n">cipher</span><span class="o">-</span><span class="k">column</span><span class="p">:</span> <span class="k">true</span>
  <span class="k">sql</span><span class="o">-</span><span class="k">show</span><span class="p">:</span> <span class="k">true</span>
  <span class="k">check</span><span class="o">-</span><span class="k">table</span><span class="o">-</span><span class="n">metadata</span><span class="o">-</span><span class="n">enabled</span><span class="p">:</span> <span class="k">false</span>
</code></pre>
     </div>
     <p>
      config-sharding.yaml
     </p>
     <div class="codehilite">
      <pre><span></span><code><span class="c1">######################################################################################################</span>
<span class="c1">#</span>
<span class="c1"># If you want to connect to MySQL, you should manually copy MySQL driver to lib directory.</span>
<span class="c1">#</span>
<span class="c1">######################################################################################################</span>

<span class="nt">schemaName</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">sharding_db</span>

<span class="nt">dataSourceCommon</span><span class="p">:</span>
  <span class="nt">username</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">root</span>
  <span class="nt">password</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">root</span>
  <span class="nt">connectionTimeoutMilliseconds</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">30000</span>
  <span class="nt">idleTimeoutMilliseconds</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">60000</span>
  <span class="nt">maxLifetimeMilliseconds</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">1800000</span>
  <span class="nt">maxPoolSize</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">50</span>
  <span class="nt">minPoolSize</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">1</span>
  <span class="nt">maintenanceIntervalMilliseconds</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">30000</span>

<span class="nt">dataSources</span><span class="p">:</span>
  <span class="nt">ds_0</span><span class="p">:</span>
    <span class="nt">url</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">jdbc:mysql://127.0.0.1:3311/demo_ds_0?serverTimezone=UTC&amp;useUnicode=true&amp;characterEncoding=utf-8&amp;useSSL=false&amp;allowPublicKeyRetrieval=true</span>
  <span class="nt">ds_1</span><span class="p">:</span>
    <span class="nt">url</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">jdbc:mysql://127.0.0.1:3312/demo_ds_1?serverTimezone=UTC&amp;useUnicode=true&amp;characterEncoding=utf-8&amp;useSSL=false&amp;allowPublicKeyRetrieval=true</span>

<span class="nt">rules</span><span class="p">:</span>
<span class="p p-Indicator">-</span> <span class="kt">!SHARDING</span>
  <span class="nt">tables</span><span class="p">:</span>
    <span class="nt">t_order</span><span class="p">:</span>
      <span class="nt">actualDataNodes</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">ds_${0..1}.t_order_${0..15}</span>
      <span class="nt">tableStrategy</span><span class="p">:</span>
        <span class="nt">standard</span><span class="p">:</span>
          <span class="nt">shardingColumn</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">order_id</span>
          <span class="nt">shardingAlgorithmName</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">t_order_inline</span>
<span class="c1">#      keyGenerateStrategy:</span>
<span class="c1">#        column: order_id</span>
<span class="c1">#        keyGeneratorName: snowflake</span>
<span class="c1">#    t_order_item:</span>
<span class="c1">#      actualDataNodes: ds_${0..1}.t_order_item_${0..1}</span>
<span class="c1">#      tableStrategy:</span>
<span class="c1">#        standard:</span>
<span class="c1">#          shardingColumn: order_id</span>
<span class="c1">#          shardingAlgorithmName: t_order_item_inline</span>
<span class="c1">#      keyGenerateStrategy:</span>
<span class="c1">#        column: order_item_id</span>
<span class="c1">#        keyGeneratorName: snowflake</span>
<span class="c1">#  bindingTables:</span>
<span class="c1">#    - t_order,t_order_item</span>
  <span class="nt">defaultDatabaseStrategy</span><span class="p">:</span>
    <span class="nt">standard</span><span class="p">:</span>
      <span class="nt">shardingColumn</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">user_id</span>
      <span class="nt">shardingAlgorithmName</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">database_inline</span>
<span class="c1">#  defaultTableStrategy:</span>
<span class="c1">#    none:</span>
<span class="c1">#  </span>
  <span class="nt">shardingAlgorithms</span><span class="p">:</span>
    <span class="nt">database_inline</span><span class="p">:</span>
      <span class="nt">type</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">INLINE</span>
      <span class="nt">props</span><span class="p">:</span>
        <span class="nt">algorithm-expression</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">ds_${user_id % 2}</span>
    <span class="nt">t_order_inline</span><span class="p">:</span>
      <span class="nt">type</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">INLINE</span>
      <span class="nt">props</span><span class="p">:</span>
        <span class="nt">algorithm-expression</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">t_order_${order_id % 16}</span>
<span class="c1">#    t_order_item_inline:</span>
<span class="c1">#      type: INLINE</span>
<span class="c1">#      props:</span>
<span class="c1">#        algorithm-expression: t_order_item_${order_id % 2}</span>
<span class="c1">#  </span>
<span class="c1">#  keyGenerators:</span>
<span class="c1">#    snowflake:</span>
<span class="c1">#      type: SNOWFLAKE</span>
<span class="c1">#      props:</span>
<span class="c1">#        worker-id: 123</span>
</code></pre>
     </div>
     <p>
      OK，一切准备就绪，直接进入sharding的根目录下的bin目录中运行：start.bat即可（也可以在命令行中运行）
     </p>
     <p>
      ```shell script
     </p>
     <h1 id="_3">
      使用命令行运行可以指定运行端口
     </h1>
     <p>
      ./start.bat 13306
     </p>
     <div class="codehilite">
      <pre><span></span><code><span class="o">&amp;</span><span class="n">ensp</span><span class="p">;</span><span class="o">&amp;</span><span class="n">ensp</span><span class="p">;</span><span class="o">&amp;</span><span class="n">ensp</span><span class="p">;</span><span class="o">&amp;</span><span class="n">ensp</span><span class="p">;</span><span class="err">成功以后刷刷刷的一排日志打出，没有错误就说明可以运行了</span>

<span class="o">&amp;</span><span class="n">ensp</span><span class="p">;</span><span class="o">&amp;</span><span class="n">ensp</span><span class="p">;</span><span class="o">&amp;</span><span class="n">ensp</span><span class="p">;</span><span class="o">&amp;</span><span class="n">ensp</span><span class="p">;</span><span class="err">使用</span><span class="n">mysql命令或者mysqlworkbench连接上sharding</span><span class="err">，运行下面的</span><span class="n">SQL语句生成测试的表</span><span class="err">，运行成功看到日志中一大批</span><span class="n">SQL语句</span><span class="err">，</span>

<span class="ss">```sql</span>
<span class="ss">CREATE TABLE IF NOT EXISTS `</span><span class="n">t_order</span><span class="ss">` (</span>
<span class="ss">    `</span><span class="n">order_id</span><span class="ss">` int(11) NOT NULL,</span>
<span class="ss">    `</span><span class="n">user_id</span><span class="ss">` int(11) NOT NULL,</span>
<span class="ss">    PRIMARY KEY (`</span><span class="n">order_id</span><span class="o">`</span><span class="p">)</span>
<span class="p">)</span> <span class="kp">ENGINE</span><span class="o">=</span><span class="n">InnoDB</span> <span class="kp">AUTO_INCREMENT</span><span class="o">=</span><span class="mi">1</span> <span class="k">DEFAULT</span> <span class="kp">CHARSET</span><span class="o">=</span><span class="n">latin1</span><span class="p">;</span>
</code></pre>
     </div>
     <h3 id="springboot-mybatis">
      SpringBoot Mybatis配置
     </h3>
     <p>
      需要修改数据库连接配置，大致如下：
     </p>
     <div class="codehilite">
      <pre><span></span><code><span class="c"># mybatis的config文件位置配置</span>
<span class="na">mybatis.config-location</span><span class="o">=</span><span class="s">classpath:mybatis/mybatis-config.xml</span>
<span class="c"># 各个表的xml文件位置配置</span>
<span class="na">mybatis.mapper-locations</span><span class="o">=</span><span class="s">classpath:mybatis/mapper/*.xml</span>
<span class="na">mybatis.type-aliases-package</span><span class="o">=</span><span class="s">com.neo.model</span>

<span class="c"># 数据库连接信息配置，自行更换数据库，用户名和密码,配置为ShardingSphereProxy</span>
<span class="na">spring.datasource.url</span><span class="o">=</span><span class="s">jdbc:mysql://localhost:13306/sharding_db?serverTimezone=UTC&amp;useUnicode=true&amp;characterEncoding=utf-8\</span>
<span class="s">  &amp;useSSL=false&amp;allowPublicKeyRetrieval=true</span>
<span class="na">spring.datasource.username</span><span class="o">=</span><span class="s">root</span>
<span class="na">spring.datasource.password</span><span class="o">=</span><span class="s">root</span>
<span class="na">spring.datasource.driver-class-name</span><span class="o">=</span><span class="s">com.mysql.cj.jdbc.Driver</span>
</code></pre>
     </div>
     <p>
      实体类、Mapper设置这里就不详细赘述了，看
      <a href="https://github.com/lw1243925457/JAVA-000/tree/main/homework/week0802/">
       GitHub上的工程
      </a>
      即可
     </p>
     <p>
      测试类进行测试，代码如下：
     </p>
     <div class="codehilite">
      <pre><span></span><code><span class="kn">package</span> <span class="nn">week0802.week0802.mappers</span><span class="p">;</span>

<span class="kn">import</span> <span class="nn">com.zaxxer.hikari.HikariDataSource</span><span class="p">;</span>
<span class="kn">import</span> <span class="nn">org.junit.jupiter.api.Test</span><span class="p">;</span>
<span class="kn">import</span> <span class="nn">org.junit.jupiter.api.extension.ExtendWith</span><span class="p">;</span>
<span class="kn">import</span> <span class="nn">org.mybatis.spring.annotation.MapperScan</span><span class="p">;</span>
<span class="kn">import</span> <span class="nn">org.springframework.beans.factory.annotation.Autowired</span><span class="p">;</span>
<span class="kn">import</span> <span class="nn">org.springframework.boot.test.context.SpringBootTest</span><span class="p">;</span>
<span class="kn">import</span> <span class="nn">org.springframework.test.context.junit.jupiter.SpringExtension</span><span class="p">;</span>
<span class="kn">import</span> <span class="nn">org.springframework.transaction.annotation.Transactional</span><span class="p">;</span>
<span class="kn">import</span> <span class="nn">week0802.week0802.models.Order</span><span class="p">;</span>

<span class="kn">import</span> <span class="nn">javax.sql.DataSource</span><span class="p">;</span>
<span class="kn">import</span> <span class="nn">java.sql.Connection</span><span class="p">;</span>
<span class="kn">import</span> <span class="nn">java.sql.ResultSet</span><span class="p">;</span>
<span class="kn">import</span> <span class="nn">java.sql.SQLException</span><span class="p">;</span>
<span class="kn">import</span> <span class="nn">java.sql.Statement</span><span class="p">;</span>
<span class="kn">import</span> <span class="nn">java.util.HashMap</span><span class="p">;</span>
<span class="kn">import</span> <span class="nn">java.util.List</span><span class="p">;</span>
<span class="kn">import</span> <span class="nn">java.util.Map</span><span class="p">;</span>

<span class="nd">@SpringBootTest</span>
<span class="nd">@ExtendWith</span><span class="p">(</span><span class="n">SpringExtension</span><span class="p">.</span><span class="na">class</span><span class="p">)</span>
<span class="nd">@MapperScan</span><span class="p">(</span><span class="s">"week0802.week0802.mappers"</span><span class="p">)</span>
<span class="kd">public</span> <span class="kd">class</span> <span class="nc">OrderMapperTest</span> <span class="p">{</span>

    <span class="nd">@Autowired</span>
    <span class="kd">private</span> <span class="n">OrderMapper</span> <span class="n">orderMapper</span><span class="p">;</span>

    <span class="cm">/**</span>
<span class="cm">     * 通过不同的查询条件的传入，可以体会到分库分表是需要设计的</span>
<span class="cm">     * 一个设计不好，查询难搞</span>
<span class="cm">     */</span>
    <span class="nd">@Test</span> <span class="nd">@Transactional</span>
    <span class="kd">public</span> <span class="kt">void</span> <span class="nf">test</span><span class="p">()</span> <span class="kd">throws</span> <span class="n">SQLException</span> <span class="p">{</span>
        <span class="c1">// 通过sharding插入数据，通过sharding自己的日志输出看出插入不同的数据库和表</span>
        <span class="n">orderMapper</span><span class="p">.</span><span class="na">insertOne</span><span class="p">(</span><span class="k">new</span> <span class="n">Order</span><span class="p">(</span><span class="mi">1L</span><span class="p">,</span> <span class="mi">1L</span><span class="p">));</span>
        <span class="n">orderMapper</span><span class="p">.</span><span class="na">insertOne</span><span class="p">(</span><span class="k">new</span> <span class="n">Order</span><span class="p">(</span><span class="mi">2L</span><span class="p">,</span> <span class="mi">2L</span><span class="p">));</span>

        <span class="c1">// 只传user_id，看到单库进行了所有表的查询</span>
        <span class="n">Map</span><span class="o">&lt;</span><span class="n">String</span><span class="p">,</span> <span class="n">Object</span><span class="o">&gt;</span> <span class="n">condition</span> <span class="o">=</span> <span class="k">new</span> <span class="n">HashMap</span><span class="o">&lt;&gt;</span><span class="p">(</span><span class="mi">1</span><span class="p">);</span>
        <span class="n">condition</span><span class="p">.</span><span class="na">put</span><span class="p">(</span><span class="s">"user_id"</span><span class="p">,</span> <span class="mi">1L</span><span class="p">);</span>

        <span class="n">List</span><span class="o">&lt;</span><span class="n">Map</span><span class="o">&lt;</span><span class="n">String</span><span class="p">,</span> <span class="n">Object</span><span class="o">&gt;&gt;</span> <span class="n">orderQuery</span> <span class="o">=</span> <span class="n">orderMapper</span><span class="p">.</span><span class="na">query</span><span class="p">(</span><span class="n">condition</span><span class="p">);</span>
        <span class="k">assert</span> <span class="n">orderQuery</span><span class="p">.</span><span class="na">size</span><span class="p">()</span> <span class="o">==</span> <span class="mi">1</span><span class="p">;</span>
        <span class="k">for</span> <span class="p">(</span><span class="n">Map</span> <span class="n">item</span><span class="p">:</span> <span class="n">orderQuery</span><span class="p">)</span> <span class="p">{</span>
            <span class="n">System</span><span class="p">.</span><span class="na">out</span><span class="p">.</span><span class="na">println</span><span class="p">(</span><span class="n">item</span><span class="p">.</span><span class="na">toString</span><span class="p">());</span>
        <span class="p">}</span>

        <span class="c1">// 只传order_id，看到进行了多库单表的查询</span>
        <span class="n">condition</span> <span class="o">=</span> <span class="k">new</span> <span class="n">HashMap</span><span class="o">&lt;&gt;</span><span class="p">(</span><span class="mi">1</span><span class="p">);</span>
        <span class="n">condition</span><span class="p">.</span><span class="na">put</span><span class="p">(</span><span class="s">"order_id"</span><span class="p">,</span> <span class="mi">1L</span><span class="p">);</span>
        <span class="n">orderQuery</span> <span class="o">=</span> <span class="n">orderMapper</span><span class="p">.</span><span class="na">query</span><span class="p">(</span><span class="n">condition</span><span class="p">);</span>
        <span class="k">assert</span> <span class="n">orderQuery</span><span class="p">.</span><span class="na">size</span><span class="p">()</span> <span class="o">==</span> <span class="mi">1</span><span class="p">;</span>
        <span class="k">for</span> <span class="p">(</span><span class="n">Map</span> <span class="n">item</span><span class="p">:</span> <span class="n">orderQuery</span><span class="p">)</span> <span class="p">{</span>
            <span class="n">System</span><span class="p">.</span><span class="na">out</span><span class="p">.</span><span class="na">println</span><span class="p">(</span><span class="n">item</span><span class="p">.</span><span class="na">toString</span><span class="p">());</span>
        <span class="p">}</span>

        <span class="c1">// 传入order_id和user_id，看到进行单库单表的查询</span>
        <span class="n">condition</span> <span class="o">=</span> <span class="k">new</span> <span class="n">HashMap</span><span class="o">&lt;&gt;</span><span class="p">(</span><span class="mi">2</span><span class="p">);</span>
        <span class="n">condition</span><span class="p">.</span><span class="na">put</span><span class="p">(</span><span class="s">"order_id"</span><span class="p">,</span> <span class="mi">2L</span><span class="p">);</span>
        <span class="n">condition</span><span class="p">.</span><span class="na">put</span><span class="p">(</span><span class="s">"user_id"</span><span class="p">,</span> <span class="mi">2L</span><span class="p">);</span>
        <span class="n">orderQuery</span> <span class="o">=</span> <span class="n">orderMapper</span><span class="p">.</span><span class="na">query</span><span class="p">(</span><span class="n">condition</span><span class="p">);</span>
        <span class="k">assert</span> <span class="n">orderQuery</span><span class="p">.</span><span class="na">size</span><span class="p">()</span> <span class="o">==</span> <span class="mi">1</span><span class="p">;</span>
        <span class="k">for</span> <span class="p">(</span><span class="n">Map</span> <span class="n">item</span><span class="p">:</span> <span class="n">orderQuery</span><span class="p">)</span> <span class="p">{</span>
            <span class="n">System</span><span class="p">.</span><span class="na">out</span><span class="p">.</span><span class="na">println</span><span class="p">(</span><span class="n">item</span><span class="p">.</span><span class="na">toString</span><span class="p">());</span>
        <span class="p">}</span>
    <span class="p">}</span>
<span class="p">}</span>
</code></pre>
     </div>
    </article>
   </div>
   <!-- Footer -->
   <footer id="footer">
    <p class="copyright">
     © Untitled. Design:
     <a href="https://html5up.net">
      HTML5 UP
     </a>
     .
    </p>
   </footer>
  </div>
  <!-- BG -->
  <div id="bg">
  </div>
  <!-- Scripts -->
  <script src="../assets/js/jquery.min.js">
  </script>
  <script src="../assets/js/browser.min.js">
  </script>
  <script src="../assets/js/breakpoints.min.js">
  </script>
  <script src="../assets/js/util.js">
  </script>
  <script src="../assets/js/main.js">
  </script>
 </body>
</html>
